﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Validation;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using EntityFramework.Extensions;
using Common.Framework.EntityMaster;
using Microsoft.Data.Extensions;
using Extend.Framework.Predicate;
using System.ComponentModel;

namespace Common.Framework.CommonDAL
{

    public class CollectionHelper
    {
        private CollectionHelper()
        {
        }

        public static DataTable ConvertTo<T>(IList<T> list)
        {
            DataTable table = CreateTable<T>();
            Type entityType = typeof(T);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            foreach (T item in list)
            {
                DataRow row = table.NewRow();

                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item);
                }

                table.Rows.Add(row);
            }

            return table;
        }

        public static IList<T> ConvertTo<T>(IList<DataRow> rows)
        {
            IList<T> list = null;

            if (rows != null)
            {
                list = new List<T>();

                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }

            return list;
        }

        public static IList<T> ConvertTo<T>(DataTable table)
        {
            if (table == null)
            {
                return null;
            }

            List<DataRow> rows = new List<DataRow>();

            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }

            return ConvertTo<T>(rows);
        }

        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();

                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value = row[column.ColumnName];
                        if (value != DBNull.Value)
                            prop.SetValue(obj, value, null);
                    }
                    catch
                    {
                        // You can log something here  
                        throw;
                    }
                }
            }

            return obj;
        }

        public static DataTable CreateTable<T>()
        {
            Type entityType = typeof(T);
            DataTable table = new DataTable(entityType.Name);
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType);

            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, prop.PropertyType);
            }

            return table;
        }
    }

    /// <summary>
    ///     EntityFramework 基于 DbContext 上下文数据访问
    /// </summary>
    /// <typeparam name="TDbContext">DbContext</typeparam>
    public class DbContextAdapter<TDbContext> : IContextAdapter
        where TDbContext : DbContext, new()
    {


        /// <summary>
        ///     获得一个实体的主键名称
        /// </summary>
        /// <param name="entityType">实体类的类型信息对象</param>
        /// <returns>主键名称</returns>
        public string GetKeyName(Type entityType)
        {
            foreach (PropertyInfo p in entityType.GetProperties())
            {
                var attr = p.GetCustomAttributes(true).FirstOrDefault() as KeyAttribute;
                if (attr != null && p.GetCustomAttributes(true).FirstOrDefault() != null)
                    return p.Name;
            }
            return string.Empty;
        }



        /// <summary>
        ///     创建外键关系关联查询对象
        /// </summary>
        /// <typeparam name="TEntity">Entity</typeparam>
        /// <param name="dbSet">查询对象上下文对象</param>
        /// <param name="includePath">关联路径</param>
        /// <returns>DbQuery</returns>
        private DbQuery<TEntity> CreatePathQuery<TEntity>(DbSet<TEntity> dbSet, string[] includePath)
            where TEntity : class
        {
            if (includePath.Length > 0)
            {

                DbQuery<TEntity> dbQuery = dbSet.Include(includePath[0]);
                for (int i = 1; i < includePath.Length; i++)
                {
                    dbQuery = dbQuery.Include(includePath[i]);
                }

                return dbQuery;

            }
            else
            {
                return dbSet;
            }
        }

        #region 创建数据上下文
        /// <summary>
        ///     创建数据上下文
        /// </summary>
        /// <returns>DbContext</returns>
        private TDbContext CreateDbContext()
        {
            var content = new TDbContext();
            content.Configuration.ProxyCreationEnabled = false;
            content.Configuration.LazyLoadingEnabled = false;
            content.Configuration.ValidateOnSaveEnabled = false;
            return content;
        }

        /// <summary>
        ///     DbContext转为ObjectContext
        /// </summary>
        /// <param name="dbContext">DbContext</param>
        /// <returns></returns>
        private ObjectContext DbContextToObjectContext(DbContext dbContext)
        {
            return ((IObjectContextAdapter)dbContext).ObjectContext;
        }

        #endregion

        #region Insert operation

        /// <summary>
        ///     添加对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entity">entity</param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int AddOrUpdateObject<TEntity>(TEntity entity, Action<SetEntityState> callDelegate = null) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);
                    setting.AddOrUpdate(entity);
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
        }

        /// <summary>
        ///     批量添加对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entityList"><![CDATA[IEnumerable<TEntity>]]></param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int AddOrUpdateObject<TEntity>(IEnumerable<TEntity> entityList, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {

                    context.Configuration.AutoDetectChangesEnabled = false; //批量插入时加上这句性能会大大提升
                    var setting = new SetEntityState(context);
                    foreach (TEntity entity in entityList)
                    {
                        setting.AddOrUpdate(entity);
                    }
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Insert operation

        /// <summary>
        ///     添加对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entity">entity</param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int AddObject<TEntity>(TEntity entity, Action<SetEntityState> callDelegate = null) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                   // context.Set<TEntity>().Add(entity)
                    var setting = new SetEntityState(context);
                    setting.Add(entity);
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     批量添加对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entityList"><![CDATA[IEnumerable<TEntity>]]></param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int AddObject<TEntity>(IEnumerable<TEntity> entityList, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {

                    context.Configuration.AutoDetectChangesEnabled = false; //批量插入时加上这句性能会大大提升
                    var setting = new SetEntityState(context);
                    foreach (TEntity entity in entityList)
                    {
                        setting.Add(entity);
                    }
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Update operation

        /// <summary>
        ///     修改对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entity">entity</param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int UpdateObject<TEntity>(TEntity entity, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    setting.Update(entity);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     批量修改
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entityList"><![CDATA[IEnumerable<TEntity>]]></param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int UpdateObject<TEntity>(IEnumerable<TEntity> entityList, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);

                    foreach (TEntity entity in entityList)
                    {
                        setting.Update(entity);
                        //context.Entry(entity).CurrentValues.SetValues(entity);
                    }

                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Update operation for EntityFramework Extensions

        /// <summary>
        ///     更新实体对象
        /// </summary>
        /// <typeparam name="TEntity">Class</typeparam>
        /// <param name="filterExpression">过滤条件</param>
        /// <param name="updateExpression">更新实体对象</param>
        /// <returns>更新记录条数</returns>
        public int UpdateObject<TEntity>(Expression<Func<TEntity, bool>> filterExpression,
            Expression<Func<TEntity, TEntity>> updateExpression) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    return context.Set<TEntity>().Update(filterExpression, updateExpression);
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }


        /// <summary>
        ///     批量更新实体对象
        /// </summary>
        /// <typeparam name="TEntity">Class</typeparam>
        /// <param name="updateExpressions">更新表达式集合</param>
        /// <returns>更新记录条数</returns>
        public int UpdateObject<TEntity>(IEnumerable<UpdateExpressionsEntity<TEntity>> updateExpressions)
            where TEntity : class, new()
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    int count = 0;
                    using (DbTransaction tx = context.BeginTransaction())
                    {
                        try
                        {
                            count =
                                updateExpressions.Sum(
                                    item => context.Set<TEntity>().Update(item.FilterExpression, item.UpdateExpression));
                            tx.Commit();
                        }
                        catch (Exception ex)
                        {
                            tx.Rollback();
                            throw ex;
                        }
                    }
                    return count;
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Delete  operation

        /// <summary>
        ///     删除对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entity">entity</param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int DeleteObject<TEntity>(TEntity entity, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);
                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    setting.Delete(entity);
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     批量删除
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entityList"><![CDATA[IEnumerable<TEntity>]]></param>
        /// <param name="callDelegate">委托函数，级联删除是可以使用</param>
        /// <returns>int</returns>
        public int DeleteObject<TEntity>(IEnumerable<TEntity> entityList, Action<SetEntityState> callDelegate = null)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);

                    //执行委托函数
                    if (callDelegate != null)
                        callDelegate(setting);
                    foreach (TEntity entity in entityList)
                    {
                        // context.Entry(entity).State = EntityState.Deleted;
                        setting.Delete(entity);
                    }
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     根据条件删除
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="expression">lamda 表达式</param>
        /// <returns></returns>
        public int DeleteObject<TEntity>(Expression<Func<TEntity, bool>> expression) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var setting = new SetEntityState(context);
                    IEnumerable<TEntity> data = GetList(expression);
                    foreach (TEntity entity in data)
                    {
                        // context.Entry(entity).State = EntityState.Deleted;
                        setting.Delete(entity);
                    }
                    return context.SaveChanges();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Delete operation for EntityFramework Extensions


        /// <summary>
        /// 计算总数
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="filterExpression">条件表达式</param>
        /// <returns></returns>
        public int Count<TEntity>(Expression<Func<TEntity, bool>> filterExpression, params string[] includePath) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    var ds = context.Set<TEntity>();
                    DbQuery<TEntity> query = CreatePathQuery(ds, includePath);
                    return query.Count(filterExpression);
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     根据条件删除
        /// </summary>
        /// <typeparam name="TEntity">class </typeparam>
        /// <param name="filterExpression">过滤表达式</param>
        /// <returns></returns>
        public int DeleteObject2<TEntity>(Expression<Func<TEntity, bool>> filterExpression) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    return context.Set<TEntity>().Delete(filterExpression);
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Select operation

        /// <summary>
        ///     根据主键值获取对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="primaryKeyValue">主键值</param>
        /// <returns>TEntity</returns>
        public TEntity GetEntity<TEntity>(object primaryKeyValue) where TEntity : class
        {
            using (TDbContext context = CreateDbContext())
            {
                ObjectContext objectContext = DbContextToObjectContext(context);
                return
                    objectContext.GetObjectByKey(new EntityKey(context.GetType().Name + "." + typeof(TEntity).Name,
                        GetKeyName(typeof(TEntity)), primaryKeyValue)) as TEntity;
            }
        }


        /// <summary>
        ///     查询单个对象
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="expression">lamda 表达式</param>
        /// <param name="includePath">关联路径</param>
        /// <returns>TEntity</returns>
        public TEntity GetEntity<TEntity>(Expression<Func<TEntity, bool>> expression, params string[] includePath)
            where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();
                    if (includePath.Length > 0)
                    {
                        DbQuery<TEntity> query = CreatePathQuery(dbSet, includePath);
                        return query.FirstOrDefault(expression);
                    }
                    return dbSet.FirstOrDefault(expression);
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw ex;
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }


        /// <summary>
        /// 查询对象集合 返回指定数量的数据
        /// </summary>
        /// <typeparam name="TEntity">
        /// class TEntity
        /// </typeparam>
        /// <param name="expression">
        /// lamda 表达式
        /// </param>
        /// <param name="sortField">
        /// 排序字段 默认为升序排列
        /// </param>
        /// <param name="totalRecord">
        /// 需要返回的数据条数
        /// </param>
        /// <param name="includePath">
        /// 关联路径
        /// </param>
        /// <returns>
        /// <![CDATA[IEnumerable<TEntity>]]>
        /// </returns>
        public IEnumerable<TEntity> GetTopList<TEntity>(
            Expression<Func<TEntity, bool>> expression,
            string sortField,
            int totalRecord,
            params string[] includePath) where TEntity : class
        {
            return this.GetTopList(expression, sortField, false, totalRecord, includePath);
        }

        /// <summary>
        /// 查询对象集合 返回指定数量的数据
        /// </summary>
        /// <typeparam name="TEntity">
        /// class TEntity
        /// </typeparam>
        /// <param name="expression">
        /// lamda 表达式
        /// </param>
        /// <param name="sortField">
        /// 排序字段 默认为升序排列
        /// </param>
        /// <param name="isDesc">
        /// 是否降序排列
        /// </param>
        /// <param name="totalRecord">
        /// 需要返回的数据条数
        /// </param>
        /// <param name="includePath">
        /// 关联路径
        /// </param>
        /// <returns>
        /// <![CDATA[IEnumerable<TEntity>]]>
        /// </returns>
        public IEnumerable<TEntity> GetTopList<TEntity>(
            Expression<Func<TEntity, bool>> expression,
            string sortField,
            bool isDesc,
            int totalRecord,
            params string[] includePath) where TEntity : class
        {
            try
            {
                using (TDbContext context = this.CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();

                    DbQuery<TEntity> query = this.CreatePathQuery(dbSet, includePath);
                    if (string.IsNullOrEmpty(sortField))
                    {
                        sortField = this.GetKeyName(typeof(TEntity));
                    }

                    return query.Where(expression).OrderBy(sortField, isDesc).Take(totalRecord).ToList();
                }
            }
            catch (DbEntityValidationException ex)
            {
                throw;
            }
            catch (Exception ex)
            {
                throw;
            }
        }



        /// <summary>
        ///     查询对象集合
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="expression">lamda 表达式</param>
        /// <param name="includePath">关联路径</param>
        /// <returns><![CDATA[IEnumerable<TEntity>]]></returns>
        public IEnumerable<TEntity> GetList<TEntity>(Expression<Func<TEntity, bool>> expression,
            params string[] includePath)
            where TEntity : class
        {
            try
            {
                var list = new List<TEntity>();
                using (TDbContext context = CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = false;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();
                    if (includePath.Length > 0)
                    {
                        DbQuery<TEntity> query = CreatePathQuery(dbSet, includePath);
                        list = query.Where(expression).ToList();
                    }
                    else
                        list = dbSet.Where(expression).ToList();
                }

                return list;
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     查询对象集合
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entsql">EF sql</param>
        /// <param name="includePath">关联路径</param>
        /// <returns><![CDATA[IEnumerable<TEntity>]]></returns>
        public IEnumerable<TEntity> GetList<TEntity>(string entsql,
            ObjectParameter[] parameters,
            params string[] includePath)
            where TEntity : class
        {
            try
            {
                var list = new List<TEntity>();
                using (TDbContext context = CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();
                    if (includePath.Length > 0)
                    {
                        ObjectQuery<TEntity> query = CreatePathQuery(dbSet, includePath).ToObjectQuery();
                        list = query.Where(entsql, parameters).ToList();
                    }
                    else
                    {
                        list = dbSet.ToObjectQuery().Where(entsql, parameters).ToList();
                    }
                }

                return list;
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }



        /// <summary>
        ///     查询分页对象集合
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="expression">条件表达式</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="totalCount">ref 返回总记录条数</param>
        /// <param name="orderByProperty">排序字段</param>
        /// <param name="isDesc">是否降序</param>
        /// <param name="includePath">关联路径</param>
        /// <returns></returns>
        public IEnumerable<TEntity> GetPager<TEntity>(Expression<Func<TEntity, bool>> expression,
            int pageIndex,
            int pageSize,
            ref long totalCount,
            string orderByProperty,
            bool isDesc,
            params string[] includePath) where TEntity : class
        {
            try
            {
                if (expression == null) expression = a => true;
                //排序字段为空时自动获取当前实体的主键作为排序字段
                if (string.IsNullOrEmpty(orderByProperty))
                {
                    orderByProperty = GetKeyName(typeof(TEntity));
                }
                using (TDbContext context = CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();
                    if (includePath != null && includePath.Length > 0)
                    {
                        DbQuery<TEntity> query = CreatePathQuery(dbSet, includePath);
                        totalCount = query.LongCount(expression);

                        return query.Where(expression)
                            .OrderBy(orderByProperty, isDesc)
                            .Skip((pageIndex - 1) * pageSize)
                            .Take(pageSize)
                            .ToList();
                    }

                    totalCount = (int)dbSet.LongCount(expression);

                    return dbSet.Where(expression)
                        .OrderBy(orderByProperty, isDesc)
                        .Skip((pageIndex - 1) * pageSize)
                        .Take(pageSize)
                        .ToList();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        /// 查询分页对象集合 支持多个排序字段
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="expression"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalCount"></param>
        /// <param name="orderByProperties">排序字段集合</param>
        /// <param name="isDesc"></param>
        /// <param name="includePath"></param>
        /// <returns></returns>
        public IEnumerable<TEntity> GetPager<TEntity>(Expression<Func<TEntity, bool>> expression,
           int pageIndex,
           int pageSize,
           ref long totalCount,
           List<OrderModelField> orderByProperties,
           params string[] includePath) where TEntity : class
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    if (expression == null) expression = a => true;
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();
                    if (includePath != null && includePath.Length > 0)
                    {
                        DbQuery<TEntity> query = CreatePathQuery(dbSet, includePath);
                        totalCount = query.LongCount(expression);

                        var queryable = query.Where(expression);
                        if (orderByProperties != null)
                        {

                            int i = 0;
                            orderByProperties.ForEach(a =>
                            {
                                //创建表达式变量参数
                                var parameter = Expression.Parameter(typeof(TEntity), "o");
                                //根据属性名获取属性
                                var property = typeof(TEntity).GetProperty(a.OrderField);
                                //创建一个访问属性的表达式
                                var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                                var orderByExp = Expression.Lambda(propertyAccess, parameter);
                                string OrderName = a.IsDesc ? "OrderByDescending" : "OrderBy";
                                if (i > 0)
                                {
                                    OrderName = a.IsDesc ? "ThenByDescending" : "ThenBy";
                                }

                                MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(TEntity), property.PropertyType }, queryable.Expression, Expression.Quote(orderByExp));
                                queryable = queryable.Provider.CreateQuery<TEntity>(resultExp);
                                i++;
                            });
                        }
                        return queryable.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList();
                    }

                    totalCount = (int)dbSet.LongCount(expression);

                    var querytemp = dbSet.Where(expression);
                    if (orderByProperties != null)
                    {
                        int i = 0;
                        orderByProperties.ForEach(a =>
                        {
                            //创建表达式变量参数
                            var parameter = Expression.Parameter(typeof(TEntity), "o");
                            //根据属性名获取属性
                            var property = typeof(TEntity).GetProperty(a.OrderField);
                            //创建一个访问属性的表达式
                            var propertyAccess = Expression.MakeMemberAccess(parameter, property);
                            var orderByExp = Expression.Lambda(propertyAccess, parameter);

                            string OrderName = a.IsDesc ? "OrderByDescending" : "OrderBy";
                            if (i > 0)
                            {
                                OrderName = a.IsDesc ? "ThenByDescending" : "ThenBy";
                            }

                            MethodCallExpression resultExp = Expression.Call(typeof(Queryable), OrderName, new Type[] { typeof(TEntity), property.PropertyType }, querytemp.Expression, Expression.Quote(orderByExp));
                            querytemp = querytemp.Provider.CreateQuery<TEntity>(resultExp);
                        });
                    }
                    return querytemp
                        .Skip((pageIndex - 1) * pageSize)
                        .Take(pageSize).ToList();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }


        /// <summary>
        ///     查询分页对象集合
        /// </summary>
        /// <typeparam name="TEntity">class TEntity</typeparam>
        /// <param name="entsql">EF SQL</param>
        /// <param name="parameters">参数</param>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">每页显示数量</param>
        /// <param name="totalCount">ref 返回总记录条数</param>
        /// <param name="orderByProperty">排序字段</param>
        /// <param name="isDesc">是否降序</param>
        /// <param name="includePath">关联路径</param>
        /// <returns></returns>
        public IEnumerable<TEntity> GetPager<TEntity>(string entsql,
            ObjectParameter[] parameters,
            int pageIndex,
            int pageSize,
            ref long totalCount,
            string orderByProperty,
            bool isDesc,
            params string[] includePath) where TEntity : class
        {
            try
            {
                //排序字段为空时自动获取当前实体的主键作为排序字段
                if (string.IsNullOrEmpty(orderByProperty))
                    orderByProperty = GetKeyName(typeof(TEntity));
                using (TDbContext context = CreateDbContext())
                {
                    context.Configuration.LazyLoadingEnabled = true;
                    context.Configuration.ProxyCreationEnabled = false;
                    DbSet<TEntity> dbSet = context.Set<TEntity>();

                    if (includePath != null && includePath.Length > 0)
                    {
                        ObjectQuery<TEntity> query = CreatePathQuery(dbSet, includePath).ToObjectQuery();
                        totalCount = query.Where(entsql, parameters).LongCount();

                        return query.Where(entsql, parameters)
                            .OrderBy(orderByProperty, isDesc)
                            .Skip((pageIndex - 1) * pageSize)
                            .Take(pageSize)
                            .ToList();
                    }

                    var objQuery = dbSet.ToObjectQuery();
                    totalCount = (int)objQuery.Where(entsql, parameters).LongCount();

                    return objQuery.Where(entsql, parameters)
                        .OrderBy(orderByProperty, isDesc)
                        .Skip((pageIndex - 1) * pageSize)
                        .Take(pageSize)
                        .ToList();
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion

        #region Other packaging

        /// <summary>
        ///     将 ObjectContext 对象暴露给业务层做任何操作。该方法开启了事务操作
        /// </summary>
        /// <param name="callDelegate">回掉函数</param>
        public virtual void ExposedDbContext(Action<TDbContext> callDelegate)
        {
            try
            {
                TDbContext context = CreateDbContext();
                if (callDelegate != null)
                {
                    callDelegate(context);
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        /// <summary>
        ///     对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="sql">命令字符串。</param>
        /// <param name="parmaeters">要应用于命令字符串的参数。SqlParameter</param>
        /// <returns>int</returns>
        public int ExecuteSqlCommand(string sql, params object[] parmaeters)
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    ObjectContext objectContext = DbContextToObjectContext(context);
                    using (objectContext.Connection.CreateConnectionScope())
                    {
                        DbCommand cmd = objectContext.CreateStoreCommand(sql, CommandType.Text, parmaeters);
                        return cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }







        /// <summary>
        ///     对数据库执行给定的 DDL/DML 命令。
        /// </summary>
        /// <param name="sql">命令字符串。</param>
        /// <param name="parmaeters">要应用于命令字符串的参数。SqlParameter</param>
        /// <returns>int</returns>
        public DataSet ExecuteSqlCommandToTables(string sql, params object[] parmaeters)
        {
            try
            {
                //DataSet ds = new DataSet();
                //DataTable dt = new DataTable();
                //using (TDbContext context = CreateDbContext())
                //{
                //    ObjectContext objectContext = DbContextToObjectContext(context);
                //    using (objectContext.Connection.CreateConnectionScope())
                //    {
                //        DbCommand cmd = objectContext.CreateStoreCommand(sql, CommandType.Text, parmaeters);
                //        cmd.CommandText = sql;
                //        cmd.Parameters.Clear();
                //        cmd.CommandType = CommandType.Text;
                //        cmd.Parameters.AddRange(parmaeters);
                //        var reader=cmd.ExecuteReader();
                //        ds.Tables.Add(dt);
                //        ds.Load(reader,LoadOption.OverwriteChanges,dt);
                //        ds.EnforceConstraints = false;
                //        return ds;
                //    }
                //}

                using (TDbContext context = CreateDbContext())
                {
                    DbCommand cmd = DbContextToObjectContext(context)
                        .CreateStoreCommand(sql, CommandType.Text, parmaeters);
                    var da = new SqlDataAdapter(cmd as SqlCommand);
                    var ds = new DataSet();
                    da.Fill(ds);
                    da.Dispose();
                    return ds;
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }
        public static IList<T> ConvertTo<T>(IList<DataRow> rows)
        {
            IList<T> list = null;

            if (rows != null)
            {
                list = new List<T>();

                foreach (DataRow row in rows)
                {
                    T item = CreateItem<T>(row);
                    list.Add(item);
                }
            }

            return list;
        }
        public static T CreateItem<T>(DataRow row)
        {
            T obj = default(T);
            if (row != null)
            {
                obj = Activator.CreateInstance<T>();

                foreach (DataColumn column in row.Table.Columns)
                {
                    PropertyInfo prop = obj.GetType().GetProperty(column.ColumnName);
                    try
                    {
                        object value = row[column.ColumnName];
                        if (value != DBNull.Value)
                            prop.SetValue(obj, value, null);
                    }
                    catch
                    {
                        // You can log something here  
                        throw;
                    }
                }
            }

            return obj;
        }

        /// <summary>
        ///     创建一个原始 SQL 查询，该查询将返回给定泛型类型的元素。类型可以是包含与从查询返回的列名匹配的属性的任何类型，也可以是简单的基元类型
        /// </summary>
        /// <typeparam name="TEntity">查询所返回对象的类型。</typeparam>
        /// <param name="sql"> SQL 查询字符串。</param>
        /// <param name="parmaeters">要应用于 SQL 查询字符串的参数。SqlParameter</param>
        /// <returns><![CDATA[IEnumerable<TEntity>]]></returns>
        public IEnumerable<TEntity> SqlQuery<TEntity>(string sql, params object[] parmaeters)
        {
            var table = ExecuteSqlCommandToTables(sql, parmaeters).Tables[0];
            //using (TDbContext context = CreateDbContext())
            //{
            //    DbCommand cmd = DbContextToObjectContext(context).CreateStoreCommand(sql, CommandType.Text, parmaeters);
            //    return cmd.Materialize<TEntity>();
            //}
            List<DataRow> rows = new List<DataRow>();
            foreach (DataRow row in table.Rows)
            {
                rows.Add(row);
            }
            return ConvertTo<TEntity>(rows);
        }

        /// <summary>
        ///     执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="parmaeters">要应用于存储过程的字符串的参数。 SqlParameter</param>
        /// <returns>dynamic</returns>
        public DataSet ExecuteProcedure(string procedureName, params object[] parmaeters)
        {
            try
            {
                using (TDbContext context = CreateDbContext())
                {
                    DbCommand cmd = DbContextToObjectContext(context)
                        .CreateStoreCommand(procedureName, CommandType.StoredProcedure, parmaeters);
                    var da = new SqlDataAdapter(cmd as SqlCommand);
                    var ds = new DataSet();
                    da.Fill(ds);
                    da.Dispose();
                    return ds;
                }
            }
            catch (DbEntityValidationException ex)
            {

                throw;
            }
            catch (Exception ex)
            {

                throw;
            }
        }

        #endregion
    }
}
